package dbAccess;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;

import constant.Constant;
import dto.DTOAbsence;
import dto.DTOClass;
import dto.DTOCompany;
import dto.DTOContact;
import dto.DTORelation;


/**
 * @author Jonathan Maret
 * Implements all interfaces related to a dto 
 * 
 */
public class ServicesImpl implements ContactService<DTOContact>, ClassService<DTOClass>, RelationService<DTORelation>, AbsenceService<DTOAbsence>, CompanyService<DTOCompany> {
	private MySQLAccess data;

	/**
	 * Create a connection with the database
	 */
	public ServicesImpl() {
		try {
			data = new MySQLAccess();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/* (non-Javadoc)
	 * @see dbAccess.ContactService#getAllContact()
	 */
	@Override
	public ArrayList<DTOContact> getAllContact() {
		ArrayList<DTOContact> ret = new ArrayList<DTOContact>();
		ResultSet resultSet = data.selectData(Constant.DATABASE_TABLE_CONTACT);

		try {

			while (resultSet.next()) {              
				ret.add(new DTOContact(
						resultSet.getInt("idcontact"),
						resultSet.getInt("company_idcompany"),
						resultSet.getString("lastname"),  
						resultSet.getString("firstname"), 
						resultSet.getString("visa"),  
						resultSet.getDate("birthdate"), 
						resultSet.getInt("typeofcontact"),
						resultSet.getString("phone"),
						resultSet.getString("email"),
						resultSet.getString("address"),
						resultSet.getString("locality"),
						resultSet.getInt("npa")
						));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ret;
	}

	/* (non-Javadoc)
	 * @see dbAccess.ContactService#getContactByVisa(java.lang.String)
	 */
	@Override
	public DTOContact getContactByVisa(String visa) {

		ResultSet resultSet = data.selectData(Constant.DATABASE_TABLE_CONTACT);
		DTOContact ret = null;

		try {
			while (resultSet.next()) {              
				if(visa.equals(resultSet.getString("visa"))){
					ret = new DTOContact(
							resultSet.getInt("idcontact"),
							resultSet.getInt("company_idcompany"),
							resultSet.getString("lastname"),  
							resultSet.getString("firstname"), 
							resultSet.getString("visa"),  
							resultSet.getDate("birthdate"), 
							resultSet.getInt("typeofcontact"),
							resultSet.getString("phone"),
							resultSet.getString("email"),
							resultSet.getString("address"),
							resultSet.getString("locality"),
							resultSet.getInt("npa")
							);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ret;
	}

	/* (non-Javadoc)
	 * @see dbAccess.ClassService#getAllClass()
	 */
	@Override
	public ArrayList<DTOClass> getAllClass() {
		ArrayList<DTOClass> ret = new ArrayList<DTOClass>();
		ResultSet resultSet = data.selectData(Constant.DATABASE_TABLE_CLASS);

		try {

			while (resultSet.next()) {              
				ret.add(new DTOClass(
						resultSet.getInt("idclass"),
						resultSet.getString("name"),
						resultSet.getInt("contact_idholder")
						));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ret;
	}

	/* (non-Javadoc)
	 * @see dbAccess.ClassService#getClassByName(java.lang.String)
	 */
	@Override
	public DTOClass getClassByName(String name) {
		ResultSet resultSet = data.selectData(Constant.DATABASE_TABLE_CLASS);
		DTOClass ret = null;

		try {
			while (resultSet.next()) {              
				if(name.equals(resultSet.getString("name"))){
					ret = new DTOClass(
							resultSet.getInt("idclass"),
							resultSet.getString("name"),
							resultSet.getInt("contact_idholder")
							);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ret;
	}

	/* (non-Javadoc)
	 * @see dbAccess.ClassService#getClassById(int)
	 */
	@Override
	public DTOClass getClassById(int id) {
		ResultSet resultSet = data.selectData(Constant.DATABASE_TABLE_CLASS);
		DTOClass ret = null;

		try {
			while (resultSet.next()) {              
				if(id == resultSet.getInt("idclass")){
					ret = new DTOClass(
							resultSet.getInt("idclass"),
							resultSet.getString("name"),
							resultSet.getInt("contact_idholder")
							);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ret;
	}

	/* (non-Javadoc)
	 * @see dbAccess.RelationService#getAllRelationByContact(dto.DTOContact)
	 */
	@Override
	public ArrayList<DTORelation> getAllRelationByContact(DTOContact contact) {
		ResultSet resultSet = data.selectData(Constant.DATABASE_TABLE_RELATION);
		ArrayList<DTORelation> ret = new ArrayList<DTORelation>();

		try {
			while (resultSet.next()) {              
				if(contact.getId() == resultSet.getInt("contact_id")){
					ret.add(new DTORelation(
							resultSet.getInt("contact_id"),
							resultSet.getInt("class_id")
							));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ret;
	}

	/* (non-Javadoc)
	 * @see dbAccess.RelationService#getAllRelationByClass(dto.DTOClass)
	 */
	@Override
	public ArrayList<DTORelation> getAllRelationByClass(DTOClass clasS) {
		ResultSet resultSet = data.selectData(Constant.DATABASE_TABLE_RELATION);
		ArrayList<DTORelation> ret = new ArrayList<DTORelation>();

		try {
			while (resultSet.next()) {              
				if(clasS.getId() == resultSet.getInt("class_id")){
					ret.add(new DTORelation(
							resultSet.getInt("contact_id"),
							resultSet.getInt("class_id")
							));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ret;
	}

	/* (non-Javadoc)
	 * @see dbAccess.RelationService#getRelation(dto.DTOContact, dto.DTOClass)
	 */
	@Override
	public DTORelation getRelation(DTOContact contact, DTOClass clasS) {
		ResultSet resultSet = data.selectData(Constant.DATABASE_TABLE_RELATION);
		DTORelation ret = null;

		try {
			while (resultSet.next()) {              
				if((clasS.getId() == resultSet.getInt("class_id") || clasS.equals(null)) && (contact.getId() == resultSet.getInt("contact_id") ||contact.equals(null))){
					ret = new DTORelation(
							resultSet.getInt("contact_id"),
							resultSet.getInt("class_id")
							);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ret;
	}

	/* (non-Javadoc)
	 * @see dbAccess.ContactService#getContactById(int)
	 */
	@Override
	public DTOContact getContactById(int id) {
		ResultSet resultSet = data.selectData(Constant.DATABASE_TABLE_CONTACT);
		DTOContact ret = null;

		try {
			while (resultSet.next()) {              
				if(id == resultSet.getInt("idcontact")){
					ret = new DTOContact(
							resultSet.getInt("idcontact"),
							resultSet.getInt("company_idcompany"),
							resultSet.getString("lastname"),  
							resultSet.getString("firstname"), 
							resultSet.getString("visa"),  
							resultSet.getDate("birthdate"), 
							resultSet.getInt("typeofcontact"),
							resultSet.getString("phone"),
							resultSet.getString("email"),
							resultSet.getString("address"),
							resultSet.getString("locality"),
							resultSet.getInt("npa")
							);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ret;
	}

	/* (non-Javadoc)
	 * @see dbAccess.AbsenceService#getAllAbsence()
	 */
	@Override
	public ArrayList<DTOAbsence> getAllAbsence() {
		ArrayList<DTOAbsence> ret = new ArrayList<DTOAbsence>();
		ResultSet resultSet = data.selectData(Constant.DATABASE_TABLE_ABSENCE);

		try {

			while (resultSet.next()) {              
				ret.add(new DTOAbsence(
						resultSet.getInt("idabsence"),
						resultSet.getInt("contact_idcontact"),
						resultSet.getDate("date"),
						resultSet.getString("period"),
						resultSet.getInt("typeofabsence"),
						resultSet.getString("comment"),
						resultSet.getBoolean("mailSent")
						));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ret;
	}

	/* (non-Javadoc)
	 * @see dbAccess.AbsenceService#updateAbsence(java.lang.Object)
	 */
	@Override
	public void updateAbsence(DTOAbsence absence) {
		ArrayList<DTOAbsence> absences =  getAbsenceByContact(getContactById(absence.getContact_idcontact()));
		boolean AbsenceExist = false;
		
		for (DTOAbsence dtoAbsence : absences) {
			if(dtoAbsence.getContact_idcontact() == absence.getContact_idcontact() && Constant.getZeroTimeDate(dtoAbsence.getDate()).equals(Constant.getZeroTimeDate(absence.getDate()))){
				AbsenceExist = true;
			}
		}

		if(AbsenceExist){
			String values = "period='" + absence.getPeriod() + "', typeofabsence=" + absence.getTypeOfAbsence() + ",comment='" + absence.getComment() + "',mailSent=" + absence.isMailSent() +" ";
			
			String condition = " contact_idcontact = " + absence.getContact_idcontact() + " AND " + "date = '" + new java.sql.Date(absence.getDate().getTime()) + "';";
			
			data.updateData(Constant.DATABASE_TABLE_ABSENCE, values, condition);
		}
		else	{
			String values = "(null,"+ absence.getContact_idcontact() + ",'" + new java.sql.Date(absence.getDate().getTime()) + "','" + absence.getPeriod() +"'," + absence.getTypeOfAbsence() + ",'" + absence.getComment() +"',"+ absence.isMailSent() +")";

			data.insertData(Constant.DATABASE_TABLE_ABSENCE, values);
		}

	} 

	/* (non-Javadoc)
	 * @see dbAccess.ClassService#updateClass(java.lang.Object, dto.DTOContact, java.util.ArrayList, java.util.ArrayList)
	 */
	@Override
	public void updateClass(DTOClass clasS, DTOContact holder, ArrayList<DTOContact> teacher, ArrayList<DTOContact> student) {
		boolean classExist = false;

		for (DTOClass dtoClass : getAllClass()) {
			if(dtoClass.getId() == clasS.getId()){
				classExist = true;
			}
		}

		if(classExist){
			String values = "name='" + clasS.getName() + "', contact_idholder=" + holder.getId() + " ";
			
			String condition = " idclass=" + clasS.getId() + ";";

			data.updateData(Constant.DATABASE_TABLE_CLASS, values, condition);
		}
		else{
			String values = "(null,'" + clasS.getName() + "'," + holder.getId() +")";

			//test for the unique key
			boolean classeUnique = true;
			for (DTOClass dtoClass : getAllClass()) {
				if(dtoClass.equals(clasS))
					classeUnique = false;
			}	
			
			if(classeUnique)
			data.insertData(Constant.DATABASE_TABLE_CLASS, values);
		}

		clasS = getClassByName(clasS.getName());

		for (DTORelation dtoRelation : getAllRelationByClass(clasS)) {
			removeRelation(dtoRelation);			
		}

		for (DTOContact dtoContact : student) {
			insertRelation(new DTORelation(dtoContact.getId(), clasS.getId()));
		}

		for (DTOContact dtoContact : teacher) {
			insertRelation(new DTORelation(dtoContact.getId(), clasS.getId()));
		}

	}

	/* (non-Javadoc)
	 * @see dbAccess.RelationService#insertRelation(java.lang.Object)
	 */
	@Override
	public void insertRelation(DTORelation relation) {
		String values = "(" + relation.getContact_id() + "," + relation.getClass_id() + ")";

		data.insertData(Constant.DATABASE_TABLE_RELATION, values);

	}

	/* (non-Javadoc)
	 * @see dbAccess.RelationService#removeRelation(java.lang.Object)
	 */
	@Override
	public void removeRelation(DTORelation relation) {
		String condition = " contact_id=" + relation.getContact_id() + " AND class_id=" + relation.getClass_id();	

		data.deleteData(Constant.DATABASE_TABLE_RELATION, condition);
	}

	/* (non-Javadoc)
	 * @see dbAccess.AbsenceService#getAbsenceByContact(dto.DTOContact)
	 */
	@Override
	public ArrayList<DTOAbsence> getAbsenceByContact(DTOContact contact) {
		ArrayList<DTOAbsence> ret = new ArrayList<DTOAbsence>();

		for (DTOAbsence dtoAbsence : getAllAbsence()) {
			if(dtoAbsence.getContact_idcontact() == contact.getId())
				ret.add(dtoAbsence);
		}


		return ret;
	}

	/* (non-Javadoc)
	 * @see dbAccess.ClassService#removeClass(java.lang.Object)
	 */
	@Override
	public void removeClass(DTOClass clasS) {

		for (DTORelation dtoRelation : getAllRelationByClass(clasS)) {
			removeRelation(dtoRelation);
		}

		String condition = " idclass=" + clasS.getId() +" ";
		data.deleteData(Constant.DATABASE_TABLE_CLASS, condition);

	}

	
	/* (non-Javadoc)
	 * @see dbAccess.ContactService#updateContact(java.lang.Object, java.util.ArrayList)
	 */
	@Override
	public void updateContact(DTOContact contact, ArrayList<DTOClass> clasS) {
		boolean contactExist = false;

		for (DTOContact dtoContact : getAllContact()) {
			if(dtoContact.getId() == contact.getId()){
				contactExist = true;
			}
		}

		if(contactExist){
			//generate the values
			String values = "lastname='" + contact.getLastname() + "'," +
					"firstname='" + contact.getFirstname() + "'," +
					"visa='" + contact.getVisa() + "'," +
					"birthdate='" + new java.sql.Date(contact.getBirthdate().getTime()) + "'," +
					"typeofcontact=" + contact.getTypeOfContact() + ",";
			if(contact.getPhone() == null)
				values += "phone='',";
			else
				values += "phone='" + contact.getPhone() + "',";

			if(contact.getEmail() == null)
				values += "email='',";
			else
				values += "email='" + contact.getEmail() + "',";

			if(contact.getAddress() == null)
				values += "address='',";
			else
				values += "address='" + contact.getAddress() +"',";

			if(contact.getCompagny() == 0)
				values += "company_idcompany=null,";
			else
				values += "company_id=" + contact.getCompagny() + ",";

			if(contact.getLocality() == null)
				values += "locality='',";
			else
				values += "locality='" + contact.getLocality() + "',";

			if(contact.getNpa() == 0)
				values += "NPA=null";
			else
				values += "NPA=" + contact.getNpa();

			String condition = " idcontact=" + contact.getId() + ";";

			data.updateData(Constant.DATABASE_TABLE_CONTACT, values, condition);
		}
		else{
			String values = "(null,";
			if(contact.getCompagny() != 0)
				values += contact.getCompagny() + ",'";
			else
				values += "null,'";
			values += contact.getLastname() + "','" + 
					contact.getFirstname() +"','" + 
					contact.getVisa() + "','" + 
					new java.sql.Date(contact.getBirthdate().getTime()) + "'," + 
					contact.getTypeOfContact() + ",'" + 
					contact.getPhone() + "','" +
					contact.getEmail() + "','" + 
					contact.getAddress() + "','" +
					contact.getLocality() + "',";
			if(contact.getNpa() != 0)
				values += contact.getNpa() +")";
			else
				values += "null)";
			
			//test for the unique key
			boolean contactUnique = true;
			for (DTOContact dtoContact : getAllContact()) {
				if(dtoContact.equals(contact))
					contactUnique = false;
			}	
			
			if(contactUnique)
			data.insertData(Constant.DATABASE_TABLE_CONTACT, values);
		}

		contact = getContactByVisa(contact.getVisa());

		for (DTORelation dtoRelation : getAllRelationByContact(contact)) {
			removeRelation(dtoRelation);			
		}

		for (DTOClass dtoClass : clasS) {
			insertRelation(new DTORelation(contact.getId(), dtoClass.getId()));
		}

	}

	/* (non-Javadoc)
	 * @see dbAccess.ContactService#removeContact(java.lang.Object)
	 */
	@Override
	public void removeContact(DTOContact contact) {
		for (DTORelation dtoRelation : getAllRelationByContact(contact)) {
			removeRelation(dtoRelation);
		}
		for (DTOAbsence dtoAbsence : getAbsenceByContact(contact)) {
			removeAbsence(dtoAbsence);
		}

		String condition = " idcontact=" + contact.getId() +" ";
		data.deleteData(Constant.DATABASE_TABLE_CONTACT, condition);

	}
	
	/* (non-Javadoc)
	 * @see dbAccess.CompanyService#getAllCompany()
	 */
	@Override
	public void removeAbsence(DTOAbsence absence) {
		String condition = "idabsence=" + absence.getId() + " ";
		data.deleteData(Constant.DATABASE_TABLE_ABSENCE, condition);
		
	}

	/* (non-Javadoc)
	 * @see dbAccess.CompanyService#getAllCompany()
	 */
	@Override
	public ArrayList<DTOCompany> getAllCompany() {
		ArrayList<DTOCompany> ret = new ArrayList<DTOCompany>();
		ResultSet resultSet = data.selectData(Constant.DATABASE_TABLE_COMPANY);

		try {

			while (resultSet.next()) {              
				ret.add(new DTOCompany(
						resultSet.getInt("idcompany"),
						resultSet.getString("name"), 
						resultSet.getString("reference"), 
						resultSet.getString("email"), 
						resultSet.getString("phone"), 
						resultSet.getString("fax"),
						resultSet.getString("address"), 
						resultSet.getString("locality"),
						resultSet.getInt("npa")
						));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ret;
	}

	/* (non-Javadoc)
	 * @see dbAccess.AbsenceService#getDailyAbsence(java.util.Calendar)
	 */
	@Override
	public ArrayList<DTOAbsence> getDailyAbsence(Calendar day) {
		ArrayList<DTOAbsence> ret = new ArrayList<DTOAbsence>();
		
		for (DTOAbsence dtoAbsence : getAllAbsence()) {
			if(Constant.getZeroTimeDate(dtoAbsence.getDate()).equals(Constant.getZeroTimeDate(day.getTime())))
				ret.add(dtoAbsence);
		}
	
		
		return ret;
	}


}